package info.unclewang.bert.support.dao;


import lombok.extern.slf4j.Slf4j;

import java.sql.*;

/**
 * @author unclewang
 * @date 2019/9/20 22:37
 */
@Slf4j
public class SemanticScholarService implements Runnable {
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/graduate?useSSL=true";
	private static final String USER = "root";
	private static final String PASSWORD = "irlab2017";
	private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
	private Long beginNum;
	private static Long intervalNum = 200000L;
	private Connection conn;
	private Statement stmt;
	private PreparedStatement pstmt;

	private static final String QUERY_SQL = "Select * from semanticScholar limit ";
	private static final String INSERT_SQL = "Insert into semanticScholar_filter(s2UrlId,paperSection,summary,paperSectionType,summaryType) values(?,?,?,?,?)";

	public SemanticScholarService(Long beginNum) {
		this.beginNum = beginNum;
		conn = initDb();
		stmt = createStmt();
		pstmt = createPstmt(conn);
	}

	private Statement createStmt() {
		try {
			return conn.createStatement();
		} catch (SQLException e) {
			log.error(e.getSQLState());
			e.printStackTrace();
		}
		return null;
	}

	public String cleanTxt(String txt) {
		txt = txt.replace(". . ", ". ").replace("..", ".");
		if (txt.startsWith(".")) {
			txt = txt.substring(1).trim();
		}
		return txt;
	}


	private String getPaperSection(String paperSection) {
		return cleanTxt(paperSection.substring(paperSection.indexOf(".")).trim());
	}

	private String getSummary(String summary) {
		return cleanTxt(summary.substring(summary.indexOf(".")).trim());
	}

	private String getPaperSectionType(String paperSection) {
		return paperSection.substring(0, paperSection.indexOf(".")).trim().toUpperCase();
	}

	private String getSummaryType(String summary) {
		return summary.substring(0, summary.indexOf(".")).trim().toUpperCase();
	}

	@Override
	public void run() {
		try {
			ResultSet rs = limitQuery(stmt);
			int id = 0;
			while (rs.next()) {

				String s2Url = rs.getString("s2Url").split("/")[4];
				String paperSection = cleanTxt(rs.getString("paperSection"));
				String summary = cleanTxt(rs.getString("summary"));
				if (paperSection.length() < 200 || summary.length() < 200) {
					continue;
				}

				pstmt.setString(1, s2Url);
				pstmt.setString(2, getPaperSection(paperSection));
				pstmt.setString(3, getSummary(summary));
				pstmt.setString(4, getPaperSectionType(paperSection));
				pstmt.setString(5, getSummaryType(summary));

				pstmt.addBatch();
				if (id % 100 == 0) {
					log.info("now is " + id);
					pstmt.executeBatch();
					conn.commit();
					pstmt.clearBatch();
				}
				id++;
			}

			log.info("now is " + id);
			pstmt.executeBatch();
			conn.commit();
			pstmt.clearBatch();

			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private Connection initDb() {
		try {
			Class.forName(DRIVER_NAME);
			Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
			connection.setAutoCommit(false);
			return connection;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	private ResultSet limitQuery(Statement stmt) {
		return limitQuery(stmt, beginNum);
	}

	private ResultSet limitQuery(Statement stmt, Long begin) {
		try {
			log.info(QUERY_SQL + begin + ", " + SemanticScholarService.intervalNum);
			return stmt.executeQuery(QUERY_SQL + begin + ", " + SemanticScholarService.intervalNum);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private PreparedStatement createPstmt(Connection conn) {
		try {
			return conn.prepareStatement(SemanticScholarService.INSERT_SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
